package com.tsugun.mediaplayer.model.database;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.tsugun.mediaplayer.model.entity.DownloadMusicModel;
import com.tsugun.mediaplayer.model.entity.SheetModel;
import com.tsugun.mediaplayer.presenter.utils.ConverterHelper;

import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Set;
import java.util.concurrent.ConcurrentHashMap;

/**
 * 数据库操作类
 * Created by shize on 2017/5/26.
 */

public class DBOperator implements DataOperator {

    // sql语句
    private static final String DB_SELECT_ALL_FAVORITE = "select * from favorite order by id asc";
    private static final String DB_INSERT_FAVORITE = "insert into favorite(musicId) values(?)";
    private static final String DB_DELETE_FAVORITE_WHERE_MUSIC_ID = "delete from favorite where musicId=?";
    private static final String DB_SELECT_ALL_LATELY = "select * from lately order by id asc";
    private static final String DB_INSERT_LATELY = "insert into lately(musicId) values(?)";
    private static final String DB_DELETE_LATELY_WHERE_MUSIC_ID = "delete from lately where musicId=?";
    private static final String DB_DELETE_ALL_LATELY = "delete from lately";
    private static final String DB_SELECT_ALL_FOLLOW = "select * from follow order by id asc";
    private static final String DB_INSERT_FOLLOW = "insert into follow(artistName) values(?)";
    private static final String DB_DELETE_FOLLOW_WHERE_ARTIST_NAME = "delete from follow where artistName=?";
    private static final String DB_SELECT_ALL_DOWNLOAD = "select * from download order by id asc";
    private static final String DB_INSERT_DOWNLOAD = "insert into download(localUrl,netUrl,imageUrl,downloadDate) values(?,?,?,?)";
    private static final String DB_DELETE_DOWNLOAD_WHERE_URL = "delete from download where url=?";
    private static final String DB_SELECT_ALL_SHEET = "select * from sheet order by id asc";
    private static final String DB_SELECT_SHEET_WHERE_TITLE = "select * from sheet where title=?";
    private static final String DB_INSERT_SHEET = "insert into sheet(title,musicIdList,imageUrl,createDate,updateDate,authorId,original) values(?,?,?,?,?,?,?)";
    private static final String DB_DELETE_SHEET_WHERE_ID = "delete from sheet where id=?";
    private static final String DB_UPDATE_SHEET = "update sheet set title=?,musicIdList=?,imageUrl=?,updateDate=? where id=?";
    private static final String DB_SELECT_ALL_PLAY_MUSIC = "select * from playList order by id asc";
    private static final String DB_INSERT_PLAY_MUSIC = "insert into playList(musicId) values(?)";
    private static final String DB_DELETE_PLAY_MUSIC_WHERE_MUSIC_ID = "delete from playList where musicId=?";
    private static final String DB_DELETE_ALL_PLAY_MUSIC = "delete from playList";
    // 字段
    private static final String ID = "id";
    private static final String TITLE = "title";
    private static final String MUSIC_ID_LIST = "musicIdList";
    private static final String CREATE_DATE = "createDate";
    private static final String UPDATE_DATE = "updateDate";
    private static final String AUTHOR_ID = "authorId";
    private static final String ORIGINAL = "original";
    private static final String IMAGE_URL = "imageUrl";
    private static final String LOCAL_URL = "localUrl";
    private static final String NET_URL = "netUrl";
    private static final String DOWNLOAD_DATE = "downloadDate";
    private static final String MUSIC_ID = "musicId";
    private static final String ARTIST_NAME = "artistName";

    // 数据库工具类
    private final DBOpenOperator mDBHelper;

    public DBOperator(Context context, String dbName, int version) {
        mDBHelper = new DBOpenOperator(context, dbName, null, version);
    }

    @Override
    public List<String> getFavorite() {
        return getStrings(DB_SELECT_ALL_FAVORITE, null, MUSIC_ID);
    }

    @Override
    public void addFavorite(String musicId) {
        execDBSQL(DB_INSERT_FAVORITE, new Object[]{musicId});
    }

    @Override
    public void deleteFavorite(String musicId) {
        execDBSQL(DB_DELETE_FAVORITE_WHERE_MUSIC_ID, new Object[]{musicId});
    }

    @Override
    public List<String> getLately() {
        return getStrings(DB_SELECT_ALL_LATELY, null, MUSIC_ID);
    }

    @Override
    public void addLately(String musicId) {
        execDBSQL(DB_INSERT_LATELY, new Object[]{musicId});
    }

    @Override
    public void deleteLately(String musicId) {
        execDBSQL(DB_DELETE_LATELY_WHERE_MUSIC_ID, new Object[]{musicId});
    }

    @Override
    public void clearLately() {
        execDBSQL(DB_DELETE_ALL_LATELY, null);
    }

    @Override
    public List<String> getFollow() {
        return getStrings(DB_SELECT_ALL_FOLLOW, null, ARTIST_NAME);
    }

    @Override
    public void addFollow(String artistName) {
        execDBSQL(DB_INSERT_FOLLOW, new Object[]{artistName});
    }

    @Override
    public void deleteFollow(String artistName) {
        execDBSQL(DB_DELETE_FOLLOW_WHERE_ARTIST_NAME, new Object[]{artistName});
    }

    @Override
    public List<DownloadMusicModel> getDownloadMusic() {
        return getDownloadRecord(DB_SELECT_ALL_DOWNLOAD, null);
    }

    @Override
    public void addDownloadMusic(DownloadMusicModel downloadMusicModel) {
        execDBSQL(DB_INSERT_DOWNLOAD, new Object[]{downloadMusicModel.getLocalUrl(),
                downloadMusicModel.getNetUrl(), downloadMusicModel.getImageUrl(),
                downloadMusicModel.getDownloadDate()});
    }

    @Override
    public void deleteDownloadMusic(String url) {
        execDBSQL(DB_DELETE_DOWNLOAD_WHERE_URL, new Object[]{url});
    }

    @Override
    public List<SheetModel> getSheet() {
        return getSheetList(DB_SELECT_ALL_SHEET, null);
    }

    @Override
    public String addSheet(SheetModel sheetModel) {
        execDBSQL(DB_INSERT_SHEET, new Object[]{sheetModel.getTitle(),
                ConverterHelper.INSTANCE.getMusicIdString(sheetModel.getMusicIdList()),
                sheetModel.getImageUrl(), sheetModel.getCreateDate(), sheetModel.getUpdateDate(),
                sheetModel.getAuthorId(), sheetModel.getOriginal()});
        return getStrings(DB_SELECT_SHEET_WHERE_TITLE, new String[]{sheetModel.getTitle()}, ID).get(0);
    }

    @Override
    public void deleteSheet(String sheetId) {
        execDBSQL(DB_DELETE_SHEET_WHERE_ID, new Object[]{sheetId});
    }

    @Override
    public void updateSheet(SheetModel sheetModel) {
        execDBSQL(DB_UPDATE_SHEET, new Object[]{sheetModel.getTitle(),
                ConverterHelper.INSTANCE.getMusicIdString(sheetModel.getMusicIdList()),
                sheetModel.getImageUrl(), sheetModel.getUpdateDate(), sheetModel.getId()});
    }

    @Override
    public List<String> getPlayList() {
        return getStrings(DB_SELECT_ALL_PLAY_MUSIC, null, MUSIC_ID);
    }

    @Override
    public void clearPlayList() {
        execDBSQL(DB_DELETE_ALL_PLAY_MUSIC, null);
    }

    @Override
    public void addMusicToPlayList(String musicId) {
        execDBSQL(DB_INSERT_PLAY_MUSIC, new Object[]{musicId});
    }

    @Override
    public void deleteMusicFromPlayList(String musicId) {
        execDBSQL(DB_DELETE_PLAY_MUSIC_WHERE_MUSIC_ID, new Object[]{musicId});
    }

    /**
     * 查询数据
     *
     * @param sql    语句
     * @param filter 条件
     * @param args   字段
     * @return 查询对象
     */
    private List<String> getStrings(String sql, String[] filter, String args) {
        SQLiteDatabase db = mDBHelper.getReadableDatabase();
        Cursor cursor = db.rawQuery(sql, filter);
        List<String> list = new ArrayList<>();
        while (cursor.moveToNext()) {
            list.add(cursor.getString(cursor.getColumnIndex(args)));
        }
        cursor.close();
        db.close();
        return list;
    }

    /**
     * 获取下载歌曲记录
     *
     * @param sql    语句
     * @param filter 条件
     * @return 下载歌曲记录
     */
    private List<DownloadMusicModel> getDownloadRecord(String sql, String[] filter) {
        SQLiteDatabase db = mDBHelper.getReadableDatabase();
        Cursor cursor = db.rawQuery(sql, filter);
        List<DownloadMusicModel> list = new ArrayList<>();
        while (cursor.moveToNext()) {
            list.add(new DownloadMusicModel(cursor.getInt(cursor.getColumnIndex(ID)),
                    cursor.getString(cursor.getColumnIndex(LOCAL_URL)),
                    cursor.getString(cursor.getColumnIndex(NET_URL)),
                    cursor.getString(cursor.getColumnIndex(IMAGE_URL)),
                    cursor.getString(cursor.getColumnIndex(DOWNLOAD_DATE))));
        }
        cursor.close();
        db.close();
        return list;
    }

    /**
     * 获取歌单
     *
     * @param sql    语句
     * @param filter 条件
     * @return 歌单
     */
    private List<SheetModel> getSheetList(String sql, String[] filter) {
        SQLiteDatabase db = mDBHelper.getReadableDatabase();
        Cursor cursor = db.rawQuery(sql, filter);
        List<SheetModel> list = new ArrayList<>();
        while (cursor.moveToNext()) {
            list.add(new SheetModel(cursor.getString(cursor.getColumnIndex(ID)),
                    cursor.getString(cursor.getColumnIndex(TITLE)),
                    ConverterHelper.INSTANCE.getMusicIdList(cursor.getString(cursor.getColumnIndex(MUSIC_ID_LIST))),
                    cursor.getString(cursor.getColumnIndex(IMAGE_URL)),
                    cursor.getString(cursor.getColumnIndex(CREATE_DATE)),
                    cursor.getString(cursor.getColumnIndex(UPDATE_DATE)),
                    cursor.getString(cursor.getColumnIndex(AUTHOR_ID)),
                    ConverterHelper.INSTANCE.strToBoolean(cursor.getString(cursor.getColumnIndex(ORIGINAL)))));
        }
        cursor.close();
        db.close();
        return list;
    }

    /**
     * 插入数据
     *
     * @param sql    语句
     * @param filter 字段值
     */
    private void execDBSQL(String sql, Object[] filter) {
        SQLiteDatabase db = mDBHelper.getWritableDatabase();
        if (filter == null) {
            db.execSQL(sql);
        } else {
            db.execSQL(sql, filter);
        }
//        db.close();
    }
}
